package com.scx.engine.convert.interf.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.session.Configuration;
import org.bson.Document;

import com.alibaba.druid.pool.DruidPooledConnection;
import com.scx.constant.Common;
import com.scx.constant.ConvertEnum;
import com.scx.engine.SqlProcessor;
import com.scx.engine.convert.interf.IConverter;
import com.scx.utils.JdbcUtil;

/**
 * 
 * MyqlConverter  
 * <p>mysql的解析器。</p> 
 * @author sunChangXing
 * @version v1.0.0
 * <p><B>last update </B> by sunChangXing @ 2018年1月4日</p>
 * @since v1.0.0
 */
public class MysqlConverter implements IConverter{
	private final static String  LEFT_JOIN = "left join";
	private final static String  INNER_JOIN = "inner join";
	private final static String  WHERE = "where";
	private final static String  SELECT = "select";
	private final static String  FROM = "from";
	private final static String  AS = "as";
	private final static String  ON = "on";
	/**获取数据库名称*/
	private static String DATABASENAME;
	
	public MysqlConverter(String dataBaseName) {
		this.DATABASENAME = dataBaseName;
	}
	
	private static enum TableKey {
		tableAlias, columnName, columnAlias
	} 
	
	
	private static final String SELECT_COMMENT_SQL = "SELECT c.COLUMN_COMMENT FROM information_schema.COLUMNS c WHERE c.TABLE_SCHEMA = '1' AND c.TABLE_NAME = '2' AND c.COLUMN_NAME = '3';";
	
	
	private Set<Map<String, String>> convertTable(String tableSql) {
		// 表名集合
		Set<Map<String, String>> arrTables = new HashSet<Map<String, String>>();
		String[] leftJoinSplitArrs = tableSql.split(LEFT_JOIN);
		for (String leftJoinStr : leftJoinSplitArrs) {
			String[] innerJoinSplitArrs = leftJoinStr.split(INNER_JOIN);
			for (String innerJoinStr : innerJoinSplitArrs) {
				// 去除on后面的字段
				int onIndex = innerJoinStr.indexOf(ON);
				String tableNameAndAlias = innerJoinStr.trim();
				if (onIndex > - 1) {
					tableNameAndAlias = innerJoinStr.substring(0, onIndex).trim();
				} 
				int asIndex = tableNameAndAlias.indexOf(AS);
				String tableName = tableNameAndAlias;
				String alias = null;
				if (asIndex > -1) {
					String[] tableNameAndAliasArr = tableNameAndAlias.split(AS);
					tableName = tableNameAndAliasArr[0].trim();
					alias = tableNameAndAliasArr[1].trim();
				}
				Map<String, String> tablesMap = new HashMap<String, String>(1);
				if (null == alias) {
					alias = tableName;
				} 
				tablesMap.put(tableName, alias);
				arrTables.add(tablesMap);
			}
		}
		return arrTables;
	}
	
	private List<Map<String, String>> convertColumn(Set<Map<String, String>> arrTables, String columnSql) throws Exception {
		List<Map<String, String>> arrColumn = new ArrayList<Map<String, String>>();
		String tableAlias = null;
		// 列别名
		String columnAlias = null;
		// 列名称
		String columnName = null;
		int commaIndex = columnSql.indexOf(Common.COMMA);
		Map<String, String> columnMap = null;
		if ("*".equals(columnSql)) {
			columnMap = new HashMap<String, String>();
			// 获得当前表的全部列（在单表的前提下）
			if (!arrTables.isEmpty() && 1 != arrTables.size()) {
				throw new Exception("当前sql查询全部当时，from后未解析到任何表信息");
			}
			tableAlias = null;
			columnAlias = null;
			columnName = columnSql;
			columnMap.put(TableKey.tableAlias.name(), tableAlias);
			columnMap.put(TableKey.columnName.name(), columnName);
			columnMap.put(TableKey.columnAlias.name(), columnAlias);
			arrColumn.add(columnMap);
		} else {
			String[] fuzzyColumns = {columnSql};
			if (commaIndex > -1) {
				fuzzyColumns = columnSql.split(Common.COMMA);
			}
			for (String fuzzyColumn : fuzzyColumns) {
				columnMap = new HashMap<String, String>();
				fuzzyColumn = fuzzyColumn.trim();
				// 数据格式des.model as model
				
				columnName = fuzzyColumn.trim();
				int aliasIndex = fuzzyColumn.indexOf(AS);
				if (aliasIndex > -1) {
					columnAlias = fuzzyColumn.substring(aliasIndex + 2, fuzzyColumn.length());
				}
				int pointIndex = fuzzyColumn.indexOf(Common.POINT);
				if (pointIndex > -1) {
					// 第一个字节到点的数据
					tableAlias = fuzzyColumn.substring(0, pointIndex);
					// 点到第一个空格的数据
					int nullIndex = fuzzyColumn.indexOf(AS);
					if (nullIndex > -1) {
						columnName = fuzzyColumn.substring(pointIndex + 1, nullIndex).trim();
					} else {
						columnName = fuzzyColumn.substring(pointIndex + 1, fuzzyColumn.length()).trim();
					}
				}
				if (null == columnAlias) {
					columnAlias = columnName.trim();
				}
				columnMap.put(TableKey.tableAlias.name(), tableAlias);
				columnMap.put(TableKey.columnName.name(), columnName);
				columnMap.put(TableKey.columnAlias.name(), columnAlias);
				arrColumn.add(columnMap);
			}
		}
		return arrColumn;
	}
	
	/**
	 * 
	 * 获取数据对象。
	 * <p>[{"表名":[{"别名", "列名"}]}, {"表名":[{"别名", "列名"}]}]</p>
	 * @param arrTables 表集合
	 * @param arrColumn 列集合
	 * @return
	 */
	private List<Map<String, Set<Map<String, String>>>> getDataList(Set<Map<String, String>> arrTables, List<Map<String, String>> arrColumn) {
		List<Map<String, Set<Map<String, String>>>> result = new ArrayList<Map<String, Set<Map<String, String>>>>();
		// 先将当前用到的表全部塞进去，并初始值null
		for (Map<String, String> map : arrTables) {
			Map<String, Set<Map<String, String>>> init = new HashMap<String, Set<Map<String, String>>>();
			init.put(map.keySet().iterator().next(), new HashSet<Map<String, String>>());
			result.add(init);
		}
		
		for (Map<String, Set<Map<String, String>>> everyInfo : result) {
			Set<String> tableNames = everyInfo.keySet();
			Iterator<String> iterator = tableNames.iterator();
			while(iterator.hasNext()) {
				String tableName = iterator.next();
				String alias = null;
				for (Map<String, String> map : arrTables) {
					if (map.keySet().contains(tableName)) {
						alias = map.get(map.keySet().iterator().next());
					}
				}
				if (null == alias) {
					break;
				}
				Set<Map<String, String>> everyColumns = everyInfo.get(tableName);
				int count = arrColumn.size();
				for (int i = 0; i < count; i++) {
					Map<String, String> oneColumnMap = arrColumn.get(i);
					String newAlias = oneColumnMap.get(TableKey.tableAlias.name());
					if (null != newAlias && newAlias.equals(alias)) {
						Map<String, String> newMap = new HashMap<String, String>();
						newMap.put(TableKey.columnAlias.name(), oneColumnMap.get(TableKey.columnAlias.name()));
						newMap.put(TableKey.columnName.name(), oneColumnMap.get(TableKey.columnName.name()));
						everyColumns.add(newMap);
					}
				}
				
			}
		}
		return result;
	}
	
	private static List<Map<String, String>> getCommont (List<Map<String, Set<Map<String, String>>>> result) {
		JdbcUtil dbp = JdbcUtil.getInstance();
		List<Map<String, String>> resultList = new ArrayList<Map<String, String>>();
		try {
			DruidPooledConnection con = dbp.getConnection();
			for (Map<String, Set<Map<String, String>>> map : result) {
				String tableName = map.keySet().iterator().next();
				for (Map<String, String> params : map.get(tableName)) {
					String columnName = params.get(TableKey.columnName.name());
					String columnAlias = params.get(TableKey.columnAlias.name());
					String sql = SELECT_COMMENT_SQL;
					sql = sql.replace("1", DATABASENAME);
					sql = sql.replace("2", tableName);
					sql = sql.replace("3", columnName);
					PreparedStatement ps = con.prepareStatement(sql);
					ps.executeQuery();
					ResultSet resultSet = ps.getResultSet();
					String comment = "";
					while (resultSet.next()) {
						comment = resultSet.getString(1);
					}
					ps.close();
					Map<String, String> resultMap = new HashMap<String, String>();
					resultMap.put(columnAlias.trim(), comment);
					resultList.add(resultMap);
				}
			}
			con.close();
			dbp = null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultList;
	}
	
	@Override
	public Document getDoc(String sql) throws Exception {
		Document resultDoc = new Document();
		sql = sql.trim().toLowerCase();
		// 判断当前执行语句（只有查询做文档），非查询的需要做记录
		// 查询select在文件中的位置
		if (sql.startsWith(SELECT)) {
			int whereIndex = sql.indexOf(WHERE);
			String tables = "";
			if (whereIndex > -1) {
				tables = sql.substring(sql.indexOf(FROM) + 4, whereIndex).trim();
			} else {
				tables = sql.substring(sql.indexOf(FROM) + 4, sql.length()).trim();
			}
			// 表名集合
			Set<Map<String, String>> arrTables = convertTable(tables);
			// 列名集合
			String columns = sql.substring(sql.indexOf(SELECT) + 6, sql.indexOf(FROM)).trim();
			List<Map<String, String>> arrColumn = convertColumn(arrTables, columns);
			List<Map<String, Set<Map<String, String>>>> result = getDataList(arrTables, arrColumn);
			resultDoc.append(ConvertEnum.DOC.name(), getCommont(result));
			resultDoc.append(ConvertEnum.RETURNCOLUMNS.name(), columns);
		}
		return resultDoc;
	}
	
	public static void main(String[] args) throws Exception {
		String  aString = "SELECT dss.FULLNAME, dss.SHORTNAME FROM d_st_structure as dss";
	}
}
